﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using System.Web.UI.WebControls;


namespace BAL
{
   public  class Vocation
    {

       public static string con = BAL.conn.connectionString;

       public static int InsertTempVocation(int IdUser, string Voc, string Desc)
       {
           SqlParameter[] arrParam = new SqlParameter[3];

           arrParam[0] = new SqlParameter("@VocationName", SqlDbType.VarChar);
           arrParam[0].Value = Voc;
           arrParam[1] = new SqlParameter("@Description", SqlDbType.VarChar);
           arrParam[1].Value = Desc ;
           arrParam[2] = new SqlParameter("@IDUser", SqlDbType.Int);
           arrParam[2].Value = IdUser;

           int IDTempVocation = Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_InsertTempVocation", arrParam));

           return IDTempVocation;
        }
       public static DataSet GetGroupsByUser(int IdUser)
       {
           SqlParameter[] arrParam = new SqlParameter[1];
           arrParam[0] = new SqlParameter("@idUser", SqlDbType.Int);
           arrParam[0].Value = IdUser;


           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetAllGroupByUser", arrParam);

           return ds;

       }
       public static DataSet GetPeapoleByIndustry(string industry)
       {

           SqlParameter[] arrParam = new SqlParameter[1];
           arrParam[0] = new SqlParameter("@industry", SqlDbType.VarChar);
           arrParam[0].Value = industry;          
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetPeapoleByIndustry", arrParam);
           return ds;
       }

       public static DataSet GetPeapoleByVocation(string vocation)
       {

           SqlParameter[] arrParam = new SqlParameter[1];
           arrParam[0] = new SqlParameter("@vocation", SqlDbType.VarChar);
           arrParam[0].Value = vocation;
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetPeapoleByVocation", arrParam);
           return ds;
       }

       public static DataSet GetAllVocation(string strSearch)
       {
           SqlParameter[] arrParam = new SqlParameter[1];
           arrParam[0] = new SqlParameter("@strSearch", SqlDbType.VarChar);
           arrParam[0].Value = strSearch;
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetAllVocation", arrParam);
           return ds;
       }


       public static DataSet GetAllCountry()
       {
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetAllCountry");
           return ds;
       }

     

       public static void InsertUserVocation(int IDVocation,int IdUser)
       {
           SqlParameter[] arrParam = new SqlParameter[2];

           arrParam[0] = new SqlParameter("@IDVocation", SqlDbType.Int);
           arrParam[0].Value = IDVocation;
           arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
           arrParam[1].Value = IdUser;

           VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_InsertUserVocation", arrParam);
         //return result;
         
       }

       public static void UpdateUserVocation(int IdUser, int IDVocation)
       {
           SqlParameter[] arrParam = new SqlParameter[2];

           arrParam[0] = new SqlParameter("@IDVocation", SqlDbType.VarChar);
           arrParam[0].Value = IDVocation;
           arrParam[1] = new SqlParameter("@IDUser", SqlDbType.VarChar);
           arrParam[1].Value = IdUser;

           VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_UpdateUserVocation", arrParam);

       }


       public static void ConfirmCustomer(int Customer_ID, string Verifi_code)
       {
           SqlParameter[] arrParam = new SqlParameter[2];

           arrParam[0] = new SqlParameter("@Customer_ID", SqlDbType.Int);
           arrParam[0].Value = Customer_ID;
           arrParam[1] = new SqlParameter("@Verifi_code", SqlDbType.VarChar);
           arrParam[1].Value = Verifi_code;

           VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_confirmUser", arrParam);

         
       }
       public static int InsertBlogTopic(string topicname,int IDIndustry, int vocation,string topicdescription, int IdUser)
       {
           SqlParameter[] arrParam = new SqlParameter[5];

           arrParam[0] = new SqlParameter("@topicname", SqlDbType.VarChar);
           arrParam[0].Value = topicname;
           arrParam[1] = new SqlParameter("@IDIndustry", SqlDbType.Int);
           arrParam[1].Value =IDIndustry ;
           arrParam[2] = new SqlParameter("@vocation", SqlDbType.Int);
           arrParam[2].Value =vocation;
           arrParam[3] = new SqlParameter("@topicdescription", SqlDbType.VarChar);
           arrParam[3].Value = topicdescription;
           arrParam[4] = new SqlParameter("@IdUser", SqlDbType.Int);
           arrParam[4].Value = IdUser;

           int val=Convert.ToInt32( VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_InsertBlogTopicdetails", arrParam));
           return val;
         
       }
       public static int SelectIsMentor( int Idtopic)
       {
           SqlParameter[] arrParam = new SqlParameter[1];

           arrParam[0] = new SqlParameter("@IDUserTpoic", SqlDbType.Int);
           arrParam[0].Value = Idtopic;
           int val = Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_SelectIsMentor", arrParam));
           return val;
       }




       public static void PopulateVocation(int userid, ref DropDownList ddlVocation)
       {
           SqlParameter[] arrParam = new SqlParameter[1];

           arrParam[0] = new SqlParameter("@userId", SqlDbType.Int);
           arrParam[0].Value = userid;

           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetVocationName",arrParam);
           if (ds.Tables.Count > 0)
           {
               if (ds.Tables[0].Rows.Count > 0)
               {
                   ddlVocation.DataSource = ds.Tables[0];
                   ddlVocation.DataTextField = "VocationName";
                   ddlVocation.DataValueField = "IDVocation";
                   ddlVocation.DataBind();

                   ListItem lst = new ListItem("---Select Vocation---", "-1");
                   ddlVocation.Items.Insert(0, lst);

                   foreach (ListItem _listItem in ddlVocation.Items)
                   {
                       _listItem.Attributes.Add("title", _listItem.Text + _listItem.Value);

                   }
               }
           }

       }
    

       public static string[] SelectTopicDetails(int userid)
       {
           SqlParameter[] arrParam = new SqlParameter[1];

           arrParam[0] = new SqlParameter("@IDUserTpoic", SqlDbType.Int);
           arrParam[0].Value = userid;


           SqlDataReader sdr = VocationSpace.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "VS_SelectTopicDetails", arrParam);

           int count = sdr.FieldCount;
           string[] details = new string[count];

           while (sdr.Read())
           {
               for (int i = 0; i < count; i++)
               {
                   details[i] = sdr[i].ToString();

               }
           }
           sdr.Close();
           return details;
       }

       public static int InsertCommentDetails(int IDUserTpoic, int IDUser, string Comment)
       {
           SqlParameter[] arrParam = new SqlParameter[3];

           arrParam[0] = new SqlParameter("@IDUserTpoic", SqlDbType.VarChar);
           arrParam[0].Value = IDUserTpoic;
           arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
           arrParam[1].Value = IDUser;
           arrParam[2] = new SqlParameter("@Comment", SqlDbType.VarChar);
           arrParam[2].Value = Comment;
         


           int val = Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_InsertCommentDetails", arrParam));
           return val;

       }

       public static DataSet SelectCommentDetails(int IDUserTpoic,int IsMentor)
       {
           SqlParameter[] arrParam = new SqlParameter[2];

           arrParam[0] = new SqlParameter("@IDUserTpoic", SqlDbType.Int);
           arrParam[0].Value = IDUserTpoic;
           arrParam[1] = new SqlParameter("@IsMentor", SqlDbType.Bit);
           arrParam[1].Value = IsMentor;     
      
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_SelectCommentDetails", arrParam);
            return ds;
       }
       public static DataSet SelectVocationDetails(int IDUser)
       {
           SqlParameter[] arrParam = new SqlParameter[1];

           arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
           arrParam[0].Value = IDUser;
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_SelectVocationDetails", arrParam);
           return ds;
       }


       public static int InsertMentorsBlogTopic(string topicname, int vocation, string topicdescription, int IdUser,int IdMentor)
       {
           SqlParameter[] arrParam = new SqlParameter[5];

           arrParam[0] = new SqlParameter("@topicname", SqlDbType.VarChar);
           arrParam[0].Value = topicname;
           arrParam[1] = new SqlParameter("@vocation", SqlDbType.Int);
           arrParam[1].Value = vocation;
           arrParam[2] = new SqlParameter("@topicdescription", SqlDbType.VarChar);
           arrParam[2].Value = topicdescription;
           arrParam[3] = new SqlParameter("@IdUser", SqlDbType.Int);
           arrParam[3].Value = IdUser;
           arrParam[4] = new SqlParameter("@IDMentor", SqlDbType.Int);
           arrParam[4].Value = IdMentor;

           int value = Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_InsertMentorsTopicdetails", arrParam));
           return value;

       }
       public static DataSet  SelectVocationsDescription(int IDVocation)
       {
           SqlParameter[] arrParam = new SqlParameter[1];
           arrParam[0] = new SqlParameter("@IDVocation", SqlDbType.Int);
           arrParam[0].Value = IDVocation;
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_SelectVocationsDescription", arrParam);
            return ds;
       }

       public static string  GetVocationDescriptionByVocName(string VocationName)
       {
           SqlParameter[] arrParam = new SqlParameter[1];
           arrParam[0] = new SqlParameter("@VocationName", SqlDbType.VarChar);
           arrParam[0].Value = VocationName;
          string str =Convert.ToString(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_GetVocationDescriptionByVocName", arrParam));
          return str;
       }

       public static string GetIndustryDescriptionByIndusName(string IndustryName)
       {
           SqlParameter[] arrParam = new SqlParameter[1];
           arrParam[0] = new SqlParameter("@IndustryName", SqlDbType.VarChar);
           arrParam[0].Value = IndustryName;
           string str =Convert.ToString(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_GetIndustryDescriptionByIndusName", arrParam));
           return str;
       }


       public static DataSet  GetUserByTempVocation(int IDTempVocation)
       {
           SqlParameter[] arrParam = new SqlParameter[1];
           arrParam[0] = new SqlParameter("@IDTempVocation", SqlDbType.Int);
           arrParam[0].Value = IDTempVocation;
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetUserByTempVocation", arrParam);
           return ds;
       }

       public static DataSet GetAllTempVocation()
       {
          
           DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetAllTempVocation");
           return ds;
       }

       public static void ApproveTempVocation(int IDTempVocation, bool IsApprove, string Reason)
       {
           SqlParameter[] arrParam = new SqlParameter[3];
           arrParam[0] = new SqlParameter("@IDTempVocation", SqlDbType.Int);
           arrParam[0].Value = IDTempVocation;       
           arrParam[1] = new SqlParameter("@IsApprove", SqlDbType.Bit);
           arrParam[1].Value = IsApprove;
           arrParam[2] = new SqlParameter("@Reason", SqlDbType.VarChar);
           arrParam[2].Value = Reason;
          
           VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_ApproveTempVocation", arrParam);

       }

       public static void InsertIndustryName(int IDIndustry, int IDUser)
       {
           SqlParameter[] arrParam = new SqlParameter[2];

           arrParam[0] = new SqlParameter("@IDIndustry", SqlDbType.Int);
           arrParam[0].Value = IDIndustry;
           arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
           arrParam[1].Value = IDUser;
          VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_InsertIndustryName", arrParam);        

       }
       public static void UpdateIndustryName(int IDIndustry, int IDUser)
       {
           SqlParameter[] arrParam = new SqlParameter[2];

           arrParam[0] = new SqlParameter("@IDIndustry", SqlDbType.Int);
           arrParam[0].Value = IDIndustry;
           arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
           arrParam[1].Value = IDUser;
           VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_UpdateIndustryName", arrParam);

       }
       public static string[] GetUserTempIndustryDetails(int IDTempIndustry)
       {
           SqlParameter[] arrParam = new SqlParameter[1];

           arrParam[0] = new SqlParameter("@IDTempIndustry", SqlDbType.Int);
           arrParam[0].Value = IDTempIndustry;
           SqlDataReader sdr = VocationSpace.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "VS_GetUserTempIndustryDetails", arrParam);

           int count = sdr.FieldCount;
           string[] details = new string[count];

           while (sdr.Read())
           {
               for (int i = 0; i < count; i++)
               {
                   details[i] = sdr[i].ToString();

               }
           }
           sdr.Close();
           return details;
       }

       public static string[] GetUserTempVocationDetails(int IDTempVocation)
       {
           SqlParameter[] arrParam = new SqlParameter[1];

           arrParam[0] = new SqlParameter("@IDTempVocation", SqlDbType.Int);
           arrParam[0].Value = IDTempVocation;
           SqlDataReader sdr = VocationSpace.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "VS_GetUserTempVocationDetails", arrParam);

           int count = sdr.FieldCount;
           string[] details = new string[count];

           while (sdr.Read())
           {
               for (int i = 0; i < count; i++)
               {
                   details[i] = sdr[i].ToString();

               }
           }
           sdr.Close();
           return details;
       }
     
    }
}
